﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class VicoDealer : IVicoDealer
    {
        public DataSet Query(VicoDealerModel q)
        {
            string sql = "SELECT d.ID,d.DEALER_NAME,d.DEALER_CODE,d.PROVINCE,d.CITY,d.TEL,d.MORE_TEL ,d.ADDRESS ,d.POSTCODE ,d.CACTCONT_PRO ,d.DEALER_DESC ,d.ORDER_NUM ,d.ADDTIME,d.ADDUSER,pc.cnname as pcname,cc.cnname as ccname FROM VICO_DEALER as d left join CMS_DICTIONARY as pc on d.PROVINCE=pc.CID left join CMS_DICTIONARY as cc on d.CITY=cc.CID ";
            sql += " where 1=1";
            if (q != null)
            {
                if (!string.IsNullOrEmpty(q.Dealernam))
                {
                    sql += " and d.DEALER_NAME like '%" + StringUtil.ReplaceSecStr(q.Dealernam) + "%'" ;
                }
                if (q.City > 0)
                {
                    sql += " and d.CITY = " + q.City;
                }
                if (q.Province > 0)
                {
                    sql += " and d.PROVINCE = " + q.Province;
                }
            }
            sql += " order by pcname,ccname,ORDER_NUM,DEALER_NAME";
            return SQLHelper.Query(sql);
        }
        private const string ADD_SQL = "INSERT INTO VICO_DEALER(DEALER_NAME,DEALER_CODE,PROVINCE,CITY,TEL,MORE_TEL,ADDRESS,POSTCODE,DEALER_DESC,ORDER_NUM,ADDTIME,ADDUSER,CACTCONT_PRO) VALUES(@DEALER_NAME,@DEALER_CODE,@PROVINCE,@CITY,@TEL,@MORE_TEL,@ADDRESS,@POSTCODE,@DEALER_DESC,@ORDER_NUM,getDate(),@ADDUSER,@CACTCONT_PRO);select @@IDENTITY";
        public int Add(VicoDealerModel model)
        {
            int id = 0;
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@DEALER_NAME",SqlDbType.VarChar),
                new SqlParameter("@DEALER_CODE",SqlDbType.VarChar),
                new SqlParameter("@PROVINCE",SqlDbType.Int),
                new SqlParameter("@CITY",SqlDbType.Int),
                new SqlParameter("@TEL",SqlDbType.VarChar),
                new SqlParameter("@MORE_TEL",SqlDbType.VarChar),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar),
                new SqlParameter("@DEALER_DESC",SqlDbType.VarChar),
                new SqlParameter("@ORDER_NUM",SqlDbType.Int),
                new SqlParameter("@ADDUSER",SqlDbType.VarChar),
                new SqlParameter("@CACTCONT_PRO",SqlDbType.VarChar)
            };
            parms[0].Value = model.Dealernam;
            parms[1].Value = model.Dealercode;
            parms[2].Value = model.Province;
            parms[3].Value = model.City;
            parms[4].Value = model.Tel;
            parms[5].Value = model.Moretel;
            parms[6].Value = model.Address;
            parms[7].Value = model.Postcode;
            parms[8].Value = model.Dealerdesc;
            parms[9].Value = model.Ordernum;
            parms[10].Value = model.Adduser;
            parms[11].Value = model.Cactcontpro;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();
                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return id;
        }
        private const string UPDATE_SQL = "UPDATE VICO_DEALER SET DEALER_NAME=@DEALER_NAME,DEALER_CODE=@DEALER_CODE,PROVINCE=@PROVINCE,CITY=@CITY,TEL=@TEL,MORE_TEL=@MORE_TEL,ADDRESS=@ADDRESS,POSTCODE=@POSTCODE,DEALER_DESC=@DEALER_DESC,ORDER_NUM=@ORDER_NUM,CACTCONT_PRO=@CACTCONT_PRO where ID=@ID";
        public bool Update(VicoDealerModel model)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@DEALER_NAME",SqlDbType.VarChar),
                new SqlParameter("@DEALER_CODE",SqlDbType.VarChar),
                new SqlParameter("@PROVINCE",SqlDbType.Int),
                new SqlParameter("@CITY",SqlDbType.Int),
                new SqlParameter("@TEL",SqlDbType.VarChar),
                new SqlParameter("@MORE_TEL",SqlDbType.VarChar),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar),
                new SqlParameter("@DEALER_DESC",SqlDbType.VarChar),
                new SqlParameter("@ORDER_NUM",SqlDbType.Int),
                new SqlParameter("@CACTCONT_PRO",SqlDbType.VarChar),
                new SqlParameter("@ID",SqlDbType.Int)
            };

            parms[0].Value = model.Dealernam;
            parms[1].Value = model.Dealercode;
            parms[2].Value = model.Province;
            parms[3].Value = model.City;
            parms[4].Value = model.Tel;
            parms[5].Value = model.Moretel;
            parms[6].Value = model.Address;
            parms[7].Value = model.Postcode;
            parms[8].Value = model.Dealerdesc;
            parms[9].Value = model.Ordernum;
            
            parms[10].Value = model.Cactcontpro;
            parms[11].Value = model.Id;

            if (SQLHelper.ExecuteSql(UPDATE_SQL, parms) > 0)
            {
                return true;
            }
            return false;
        }
        private const string DEL_SQL = "DELETE FROM VICO_DEALER WHERE ID=@ID";
        public bool Del(int id)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ID",SqlDbType.Int)
            };
            parms[0].Value = id;
            if (SQLHelper.ExecuteSql(DEL_SQL, parms) > 0)
            {
                return true;
            }
            return false;
        }
        private const string GET_SQL = "SELECT DEALER_NAME,DEALER_CODE,PROVINCE,ORDER_NUM,CITY,TEL,MORE_TEL,ADDRESS,POSTCODE,DEALER_DESC,ADDTIME,ADDUSER,CACTCONT_PRO FROM VICO_DEALER WHERE ID=@ID";
        public VicoDealerModel GetModel(int id)
        {
            VicoDealerModel model = null;
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ID" ,SqlDbType.Int)
            };
            parms[0].Value = id;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_SQL, parms))
            {
                if (rdr.Read())
                {
                    model = new VicoDealerModel();
                    model.Id = id;
                    if (!rdr.IsDBNull(0)) model.Dealernam = rdr.GetString(0);
                    if (!rdr.IsDBNull(1)) model.Dealercode = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) model.Province = rdr.GetInt32(2);
                    if (!rdr.IsDBNull(3)) model.Ordernum = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) model.City = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) model.Tel = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) model.Moretel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) model.Address = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) model.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) model.Dealerdesc = rdr.GetString(9);
                    
                    if (!rdr.IsDBNull(10)) model.Addtime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) model.Adduser = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) model.Cactcontpro = rdr.GetString(12);

                }
            }

            return model;
        }



        /// <summary>
        /// CMS字典表中根据经销商省份ID，获取经销商省份名称
        /// </summary>
        /// <param name="provinceID"></param>
        /// <returns></returns>
        public string GetDealerProvince(string provinceID)
        {
            string dealerProvince = string.Empty;
            //upid=1代表字典表中的城市，固定值
            string strSql = "select cnname  from  dbo.CMS_DICTIONARY where upid=1 and cid=" + provinceID;
            DataTable dt = SQLHelper.Query(strSql, SQLHelper.CONN_STRING_NON_DTC).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                dealerProvince = dt.Rows[0][0].ToString();
            }

            return dealerProvince;
        }
        /// <summary>
        ///  CMS字典表中根据经销商省份ID、经销商城市ID，获取经销商城市名称
        /// </summary>
        /// <param name="provinceId"></param>
        /// <param name="cityID"></param>
        /// <returns></returns>
        public string GetDealerCity(string provinceId, string cityID)
        {

            string dealerCity = string.Empty;
            string strSql = "select cnname  from  dbo.CMS_DICTIONARY where upid=" + provinceId + " and cid=" + cityID;
            DataTable dt = SQLHelper.Query(strSql, SQLHelper.CONN_STRING_NON_DTC).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                dealerCity = dt.Rows[0][0].ToString();
            }

            return dealerCity;
        }

        /// <summary>
        /// 获取经销商名称
        /// </summary>
        /// <param name="dealerID"></param>
        /// <returns></returns>
        public string GetDealerName(string dealerID)
        {
            string dealerName = string.Empty;
            string strSql = "select dealer_name from dbo.VICO_DEALER where id=" + dealerID;
            DataTable dt = SQLHelper.Query(strSql, SQLHelper.CONN_STRING_NON_DTC).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                dealerName = dt.Rows[0][0].ToString();
            }

            return dealerName;
        }

        
    }
}
